{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQLAlchemy ORM\n",
    "\n",
    "Credits www.github.com/bansalkanav"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setup\n",
    "\n",
    "**Step-1:** Install the modules  \n",
    "- `pip install Flask-SQLAlchemy`  \n",
    "- `pip install Flask-Migrate`\n",
    "\n",
    "**Step-2:** Create a Flask App\n",
    "```\n",
    "from flask import Flask\n",
    "app = Flask(__name__)\n",
    "```\n",
    "\n",
    "**Step-3:** Import SQLAlchemy and Migrate module\n",
    "```\n",
    "from flask_sqlalchemy import SQLAlchemy\n",
    "from flask_migrate import Migrate\n",
    "```\n",
    "\n",
    "**Step-4:** SQLAlchemy Configuration and pass the application into SQLAlchemy class\n",
    "\n",
    "```\n",
    "basedir = os.path.abspath(os.path.dirname(__file__))\n",
    "path = 'sqlite:///' + os.path.join(basedir, 'data.sqlite')\n",
    "app.config['SQLALCHEMY_DATABASE_URI'] = path\n",
    "app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False\n",
    "\n",
    "db = SQLAlchemy(app)\n",
    "Migrate(app, db)\n",
    "```\n",
    "\n",
    "**Step-5:** Create a Model or Table\n",
    "- Create a Model Class\n",
    "- Inherit from `db.Model`\n",
    "- Provide the table name\n",
    "- Add columns in the model\n",
    "- Create `__init__` and `__repr__`\n",
    "```\n",
    "class Sabji(db.Model):\n",
    "    __tablename__ = 'sabjis'\n",
    "    id = db.Column(db.Integer, primary_key = True)\n",
    "    name = db.Column(db.Text)\n",
    "    mrp = db.Column(db.Integer)\n",
    "    def __init__(self, name, mrp):\n",
    "        self.name = name\n",
    "        self.mrp = mrp\n",
    "    def __repr__(self):\n",
    "        return \"Sabji Name - {} and MRP - {}\".format(self.name, self.mrp)\n",
    "```\n",
    "\n",
    "**Step-6:** Run the following commands to Migrate the Database  \n",
    "- `set FLASK_APP=app.py`  \n",
    "- `flask db init`  \n",
    "- `flask db migrate -m \"My first DB migrate\"`  \n",
    "- `flask db upgrade`\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Basic CRUD Operations\n",
    "\n",
    "1. Create or Insert Operation\n",
    "```\n",
    "sabji = Sabji(name='alloo', price=10)\n",
    "db.session.add(sabji)\n",
    "db.session.commit()\n",
    "```\n",
    "\n",
    "2. Read Operation\n",
    "```\n",
    "Sabji.query.all()\n",
    "```\n",
    "\n",
    "3. Update Operation\n",
    "```\n",
    "sabji = Sabji.query.get(2)\n",
    "sabji.name = 'aloo'\n",
    "db.session.commit()\n",
    "```\n",
    "\n",
    "4. Delete Operation\n",
    "```\n",
    "sabji = Sabji.query.get(2)\n",
    "db.session.delete(sabji)\n",
    "db.session.commit()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exploring Read Operation using `filter_by`\n",
    "\n",
    "**Syntax -** `MODEL_NAME.query.filter_by(MODEL_COLUMN_NAME='')`\n",
    "\n",
    "1. Getting all rows if sabji name is 'alloo' from the Database Table\n",
    "```\n",
    "Sabji.query.filter_by(name='alloo').all()\n",
    "```\n",
    "\n",
    "2. Getting only the first row if sabji name is 'alloo'\n",
    "```\n",
    "Sabji.query.filter_by(name='alloo').first()\n",
    "```\n",
    "\n",
    "3. Count of rows if sabji name is 'alloo'\n",
    "```\n",
    "Sabji.query.filter_by(name='alloo').count()\n",
    "```\n",
    "\n",
    "4. Getting rows by `id`\n",
    "```\n",
    "Sabji.query.filter_by(id=2).first()\n",
    "OR\n",
    "Sabji.query.get(2)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### More powerful queries using `filter`\n",
    "\n",
    "`filter_by` is used for simple queries on the column names using regular kwargs, like:\n",
    "```\n",
    "User.filter_by(name='Joe')\n",
    "```\n",
    "The same can be accomplished with `filter`, not using kwargs, but instead using the equality operator(==, !=, >, <, etc.), which has been overloaded on the db.users.name object:\n",
    "```\n",
    "User.filter(User.name=='Joe')\n",
    "```\n",
    "\n",
    "1. Getting all the sabji's with name not equal to 'alloo'\n",
    "```\n",
    "Sabji.query.filter(Sabji.name!='alloo').all()\n",
    "```\n",
    "\n",
    "2. LIKE\n",
    "```\n",
    "Sabji.query.filter(Sabji.name.like('%a%')).all()\n",
    "```\n",
    "\n",
    "3. IN\n",
    "```\n",
    "Sabji.query.filter(Sabji.name.in_(['alloo', 'bhindi'])).all()\n",
    "```\n",
    "\n",
    "4. AND\n",
    "```\n",
    "Sabji.query.filter(and_(Sabji.name=='alloo', Sabji.price > 10)).all()\n",
    "```\n",
    "\n",
    "5. OR\n",
    "```\n",
    "Sabji.query.filter(or_(Sabji.name=='alloo', Sabji.price > 10)).all()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Order By and Join\n",
    "\n",
    "1. ORDER BY\n",
    "```\n",
    "Sabji.query.order_by(Sabji.name).all()\n",
    "```\n",
    "\n",
    "2. ORDER BY DESC\n",
    "```\n",
    "Sabji.query.order_by(Sabji.name.desc()).all()\n",
    "```\n",
    "\n",
    "3. Join\n",
    "```\n",
    "db.session.query(Sabji, Vendor).filter(Sabji.id == Vendor.sabji_id).all()\n",
    "```\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
